package com.neuedu.jdbc.v1.dao;

import com.neuedu.jdbc.v1.entity.Emp;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 通过EmpDao生成 测试类
 */
public class EmpDao extends BaseDao {


    @Override
    public List<Map> selectList(String sql) {
        List list = new ArrayList();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//一共查询结果中有几列

            //让rs指向数据行
            while (rs.next()) {//循环行
                Map row = new HashMap();
                //循环列
                for (int i = 1; i <= columnCount; i++) {
                    String label = metaData.getColumnLabel(i);
                    Object value = rs.getObject(label);
                    row.put(label, value);
                }
                list.add(row);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.close(rs, stmt, conn);
        }

        return list;
    }


    public List<Emp> selectEmpList(String sql) {
        List list = new ArrayList();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//一共查询结果中有几列

            //让rs指向数据行
            while (rs.next()) {//循环行
                Emp emp = new Emp();
                //循环列
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emp.setMgr(rs.getInt("mgr"));
                //java.sql.TimeStamp -->  java.util.Date;
                emp.setHiredate(rs.getTimestamp("hiredate"));
                emp.setSal(rs.getDouble("sal"));
                emp.setComm(rs.getDouble("comm"));
                emp.setDeptNo(rs.getInt("deptNo"));

                list.add(emp);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.close(rs, stmt, conn);
        }

        return list;
    }

    @Override
    public Map selectById(Integer id) {
        String sql = "select * from emp where empno  = ? ";

        Map row = null;

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement(sql);
            //绑定变量
            stmt.setInt(1, id);
            rs = stmt.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//一共查询结果中有几列

            //让rs指向数据行
            if (rs.next()) {//循环行
                row = new HashMap();
                //循环列
                for (int i = 1; i <= columnCount; i++) {
                    String label = metaData.getColumnLabel(i);
                    Object value = rs.getObject(label);
                    row.put(label, value);
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            super.close(rs, stmt, conn);
        }

        return row;
    }

    @Override
    public int selectCount(String sql) {

        int count = 0;

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement(" select count(1) c from  (  " + sql + ") tab ");
            //绑定变量
            rs = stmt.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();//一共查询结果中有几列

            //让rs指向数据行
            if (rs.next()) {//循环行
                count = rs.getInt("c");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            super.close(rs, stmt, conn);
        }

        return count;
    }

    /**
     * 根据 sql  和参数更新数据
     *
     * @param sql
     * @param params
     * @return
     */
    @Override
    public int update(String sql, Object... params) {
        int count = 0;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement(sql.toString());

            //绑定变量
            for (int i = 0; i < params.length; i++) {
                stmt.setObject(i + 1, params[i]);
            }

            count = stmt.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            super.close(rs, stmt, conn);
        }
        return count;
    }

    @Override
    public int deleteById(Integer id) {
        int count = 0;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "delete from emp where empno = ? ";
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement(sql.toString());

            //绑定变量
            stmt.setObject(1, id);

            count = stmt.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            super.close(rs, stmt, conn);
        }
        return count;
    }


}
